/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package cafe.Model;

import cafe.DataObject.BillDetail;
import cafe.DataObject.DataObject;
import cafe.Model.Utility.DBConnection;
import cafe.Model.Utility.DBConnectionPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HoangLong
 */
public class BillDetailModel {

    private static BillDetailModel instance = null;
    
    private DBConnectionPool connectionPool = null;
    
    /**
     * Get all table and it's informations
     *
     * @return ArrayList<Table> all table in database
     */
    private BillDetailModel() {
        this.connectionPool = DBConnectionPool.getInstance();
    }

    public static synchronized BillDetailModel getInstance() {
        if (instance == null) {
            instance = new BillDetailModel();
        }

        return instance;
    }
     
    public List<BillDetail> getAll() {
        List<BillDetail> result = new LinkedList();

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `bill_detail`";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query);

            while (rs.next()) {
                BillDetail billDetail = new BillDetail();
                billDetail.setBillId(rs.getString("billId"));
                billDetail.setFoodId(rs.getString("foodId"));
                billDetail.setAmount(rs.getInt("amount"));
                billDetail.setPrice(rs.getLong("price"));               

                result.add(billDetail);
            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            return result;
        }
    }

    public List<BillDetail> getByBillID(String id) {
        List<BillDetail> result = new LinkedList();

        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "SELECT * FROM `bill_detail` WHERE `billId` = ?";

        ResultSet rs = null;
        try {
            rs = connectionObject.query(query, id);

            while (rs.next()) {
                BillDetail billDetail = new BillDetail();
                billDetail.setBillId(rs.getString("billId"));
                billDetail.setFoodId(rs.getString("foodId"));
                billDetail.setAmount(rs.getInt("amount"));
                billDetail.setPrice(rs.getLong("price")); 
                
                result.add(billDetail);
            }

        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

            try {
                if (null != rs) {
                    rs.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(BillModel.class.getName()).log(Level.SEVERE, null, ex);
            }
            
            return result;
        }
    }

    public int insert(BillDetail record) {
        BillDetail bill = (BillDetail) record;
        
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "INSERT INTO `bill_detail`(`billId`, `foodId`, `amount`, `price`) " +
                       "VALUES (?,?,?,?)";

        int result = 0;
        
        try {
            connectionObject.excute(query, bill.toObjectArray());

         
        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

           
            return result;
        }
    }

//    public int edit(String fromRecordId, BillDetail toRecord) {
//        BillDetail bill = (BillDetail) toRecord;
//        
//        //connect
//        DBConnection connectionObject = null;
//        try {
//            connectionObject = this.connectionPool.getConnection();
//
//        } catch (Exception ex) {
//
//        }
//
//        //NOTE: we must use ` ` to cover table name 
//        String query = "UPDATE `bill_detail` "
//                + "SET `billId`=?,`foodId`=?,`amount`=?, `price=`? "
//                + "WHERE `id` = ?";
//
//        int result = 0;
//        
//        try {
//            connectionObject.excute(query, bill.getDate(), bill.getTotalCost(), bill.getIsPaid(), bill.getCustomerId(),
//                    fromRecordId);
//
//         
//        } catch (SQLException ex) {
//
//        } finally {
//            this.connectionPool.giveBack(connectionObject);
//
//           
//            return result;
//        }
//    }

    public int delete(String id) {
        //connect
        DBConnection connectionObject = null;
        try {
            connectionObject = this.connectionPool.getConnection();

        } catch (Exception ex) {

        }

        //NOTE: we must use ` ` to cover table name 
        String query = "DELETE FROM `bill_detail` WHERE `foodId` = ?;";

        int result = 0;
        
        try {
            connectionObject.excute(query, id);

         
        } catch (SQLException ex) {

        } finally {
            this.connectionPool.giveBack(connectionObject);

           
            return result;
        }
    }
    
}
